package main

import (
	"os"
	"fmt"
	"runtime"
	"strconv"
	"database/sql"
	"github.com/tealeg/xlsx"
	_ "github.com/go-sql-driver/mysql"	
)

func checkErr(err error) {
	if err != nil {
		fmt.Println(err)
		panic(err)
	}
}

func main() {
	runtime.GOMAXPROCS(runtime.NumCPU())
	if len(os.Args) != 4 {
		fmt.Println("请按照格式输入：mysql2xlsx [DSN] [DBNAME] [*.xlsx]")
		os.Exit(-1)
	}
	
	dsn := os.Args[1]
	dbName := os.Args[2]
	fileName := os.Args[3]

	var xlFile = xlsx.NewFile()	
	
	db,err := sql.Open("mysql", dsn+"/"+dbName+"?charset=utf8")
	checkErr(err)
	
	defer db.Close()
	db.SetMaxOpenConns(100)
	db.SetMaxIdleConns(100)
	
	res,err := db.Query("SHOW TABLES;")
	checkErr(err)
	
	for res.Next() {
		var table string
		var sheet *xlsx.Sheet
		var row *xlsx.Row
		var cell *xlsx.Cell		

		res.Scan(&table)
		fmt.Println("Begin export table: " + table + ".")
		sheet, err = xlFile.AddSheet(table)
		if err != nil {
			fmt.Println(err.Error())
			continue
		}
		row = sheet.AddRow()
		cell = row.AddCell()
		cell.Value = "字段名称"
		cell = row.AddCell()
		cell.Value = "字段类型"
		cell = row.AddCell()
		cell.Value = "默认值"
		cell = row.AddCell()
		cell.Value = "最大长度"
		cell = row.AddCell()
		cell.Value = "是否可空"
		cell = row.AddCell()
		cell.Value = "是否主键"
		cell = row.AddCell()
		cell.Value = "描述"
		sqlStr := "SELECT COLUMN_NAME 字段名称, COLUMN_TYPE 字段类型, COLUMN_DEFAULT 默认值, CHARACTER_MAXIMUM_LENGTH AS 最大长度, (CASE WHEN is_nullable = 'NO' THEN '否' ELSE '是' END) AS 是否可空, (CASE WHEN column_key = 'PRI' THEN '是' ELSE '否' END) AS 是否主键, COLUMN_COMMENT 描述 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '"+dbName+ "' AND TABLE_NAME = '"+ table + "'"
		//fmt.Println("sql:" + sqlStr)
		rows, err := db.Query(sqlStr)
		checkErr(err)
		if rows != nil {			
			for rows.Next() {	
				var name sql.NullString
				var dataType sql.NullString
				var defaultValue sql.NullString
				var maxLength sql.NullInt64
				var isNullable sql.NullString
				var isColumnKey sql.NullString
				var description sql.NullString			
								
				err = rows.Scan(&name, &dataType, &defaultValue, &maxLength, &isNullable, &isColumnKey, &description)
				checkErr(err)			
				row = sheet.AddRow()
				cell = row.AddCell()
				cell.Value = name.String
			//	fmt.Println("wirite name end.")
				
				cell = row.AddCell()
				cell.Value = dataType.String
			//	fmt.Println("wirite dataType end.")

				cell = row.AddCell()
				cell.Value = defaultValue.String
			//	fmt.Println("wirite defaultValue end.")

				cell = row.AddCell()
				if maxLength.Valid {
					cell.Value = strconv.FormatInt(maxLength.Int64, 10)
				}else{
					cell.Value = ""
				}
				//cell.Value = string(maxLength.Int64)
			//	fmt.Println("wirite maxLength end.")

				cell = row.AddCell()
				cell.Value = isNullable.String
			//	fmt.Println("wirite isNullable end.")

				cell = row.AddCell()
				cell.Value = isColumnKey.String
			//	fmt.Println("wirite isColumnKey end.")

				cell = row.AddCell()
				cell.Value = description.String
			//	fmt.Println("wirite description end.")
			}
		}

		xlFile.Save(fileName)
		fmt.Println("End export table: " + table + ".")
	}
	
	
}